delimiter $$

DROP TABLE IF EXISTS sec_pm$$
DROP TABLE IF EXISTS sec_user_ext$$
DROP TABLE IF EXISTS sec_user_role$$
DROP TABLE IF EXISTS sec_user$$

DROP TABLE IF EXISTS sec_role_action$$
DROP TABLE IF EXISTS sec_role$$

delimiter $$

CREATE TABLE `sec_role` (
  `application_id` int(11) NOT NULL,
  `role_id` char(32) NOT NULL,
  `seq` int(11) NOT NULL DEFAULT '255',
  `display_name` varchar(200) NOT NULL DEFAULT '',
  `description` varchar(2000) DEFAULT '',
  PRIMARY KEY (`application_id`,`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

delimiter $$

CREATE TABLE `sec_role_action` (
  `application_id` int(11) NOT NULL,
  `role_id` char(32) NOT NULL,
  `action_id` int(11) NOT NULL,
  PRIMARY KEY (`application_id`,`role_id`,`action_id`),
  KEY `sec_role_action_related_action` (`action_id`),
  KEY `sec_role_action_related_role` (`application_id`,`role_id`),
  CONSTRAINT `sec_role_action_role_id` FOREIGN KEY (`application_id`, `role_id`) REFERENCES `sec_role` (`application_id`, `role_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

delimiter $$

CREATE TABLE `sec_user` (
  `user_id` char(32) NOT NULL,
  `seq` int(11) NOT NULL DEFAULT '255',
  `name` varchar(200) NOT NULL,
  `real_name` varchar(200) NOT NULL,
  `status` int(11) NOT NULL DEFAULT '1',
  `grp_id` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  KEY `sec_user_name_idx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

delimiter $$

CREATE TABLE `sec_user_ext` (
  `user_id` char(32) NOT NULL COMMENT 'Id of user.',
  `ext_id` int(11) NOT NULL,
  `ext_value` varchar(2000) NOT NULL DEFAULT '',
  PRIMARY KEY (`user_id`,`ext_id`),
  CONSTRAINT `sec_user_ext_user_id` FOREIGN KEY (`user_id`) REFERENCES `sec_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

delimiter $$

CREATE TABLE `sec_user_role` (
  `application_id` int(11) NOT NULL DEFAULT '1',
  `user_id` char(32) NOT NULL,
  `role_id` char(32) NOT NULL,
  PRIMARY KEY (`application_id`,`user_id`,`role_id`),
  KEY `sec_user_role_role_id` (`application_id`,`role_id`),
  KEY `sec_user_role_user_id` (`user_id`),
  CONSTRAINT `sec_user_role_role_id` FOREIGN KEY (`application_id`, `role_id`) REFERENCES `sec_role` (`application_id`, `role_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `sec_user_role_user_id` FOREIGN KEY (`user_id`) REFERENCES `sec_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

delimiter $$

CREATE TABLE `sec_pm` (
  `user_id` char(32) NOT NULL,
  `password` char(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`user_id`),
  KEY `sec_pm_user_id_idx` (`user_id`),
  CONSTRAINT `sec_pm_user_id` FOREIGN KEY (`user_id`) REFERENCES `sec_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
